﻿using System;
using System.Collections.Generic;
using System.Text;
using AD.Model;
using System.Data;
using System.Data.SqlClient;

namespace AD.DAL
{
    public class NetShopsDAL : DALBase
    {
        public static List<NetShopMod> Query(string keywords, string engine)
        {
            List<NetShopMod> accountFlows = new List<NetShopMod>();
            try
            {
                string sql = "SELECT * FROM tab_shops_info join tab_users_info on tab_shops_info.UserId = tab_users_info.AutoID" +
                       " WHERE BalancePoint>0 AND Status=1 AND IsUse=1 AND IsValid=1 AND IsFrozen=0 AND NetShopType={0} AND KeyWords LIKE '%{1}%'";

                sql = string.Format(sql, engine, keywords);
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                foreach (DataRow row in dt.Rows)
                {
                    NetShopMod user = SetModel(row);
                    if (user != null) accountFlows.Add(user);
                }
            }
            catch { }
            return accountFlows;
        }
        public static int Sum(string filter)
        {
            try
            {
                string sql = "Select count(*) from tab_shops_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                SqlDataReader reader = ExecuteReader(sql);
                while (reader.Read())
                {
                    return reader.GetInt32(0);
                }
            }
            catch { }
            return 0;
        }
        public static List<NetShopMod> QueryByPage(string filter, int pageSize, int index, out int maxCount)
        {
            maxCount = 0;
            try
            {
                maxCount = Count(filter);
                string sql = "select top {0} * from tab_shops_info where (AutoID NOT IN (select top {1} AutoID from tab_shops_info order by AutoID)) order by AutoID";
                sql = string.Format(sql, pageSize, index * pageSize);
                if (!string.IsNullOrEmpty(filter))
                {
                    sql = "select top {0} * from tab_shops_info where {2} and (AutoID NOT IN (select top {1} AutoID from tab_shops_info where {2} order by AutoID)) order by AutoID";
                    sql = string.Format(sql, pageSize, index * pageSize, filter);
                }
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<NetShopMod> accountFlows = new List<NetShopMod>();
                foreach (DataRow row in dt.Rows)
                {
                    NetShopMod user = SetModel(row);
                    user.User = UserDAL.QueryFrist(string.Format(" AutoID='{0}'", user.UserId));
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch { return null; }
        }

        public static int Count(string filter)
        {
            return QueryFilterToTable(filter).Rows.Count;
        }

        public static int Insert(NetShopMod keyword)
        {
            try
            {
                if (CheckKeyWords(keyword) != null) return 0;
                string sql = "insert into tab_shops_info(IsPublicbaby,IsUse,UserId,Commit_Date,Status,NetShopType,KeyWords,ShopName,ProductName,Url,ProductPrice,ProductPicture,CommitCount,QQ,Shipping,Region,IsLogo,IsProtection,IsRetreat,IsAllDayShipping,IsGenuineProtection,IsCashonDelivery,IsCreditCard) values(@IsPublicbaby,@IsUse,@UserId,@Commit_Date,@Status,@NetShopType,@KeyWords,@ShopName,@ProductName,@Url,@ProductPrice,@ProductPicture,@CommitCount,@QQ,@Shipping,@Region,@IsLogo,@IsProtection,@IsRetreat,@IsAllDayShipping,@IsGenuineProtection,@IsCashonDelivery,@IsCreditCard)";
                SqlParameter[] sqlParams = GetSqlParameter(keyword);
                return ExecuteNonQuery(sql, sqlParams);
            }
            catch
            {
                return 0;
            }
        }

        public static int UpdateInfo(NetShopMod keyword)
        {
            try
            {
                string sql = "update tab_shops_info set IsPublicbaby=@IsPublicbaby,UserId=@UserId,Commit_Date=@Commit_Date,Status=@Status,NetShopType=@NetShopType,KeyWords=@KeyWords,ShopName=@ShopName,ProductName=@ProductName,Url=@Url,ProductPrice=@ProductPrice,ProductPicture=@ProductPicture,CommitCount=@CommitCount,QQ=@QQ,Shipping=@Shipping,Region=@Region,IsLogo=@IsLogo,IsProtection=@IsProtection,IsRetreat=@IsRetreat,IsAllDayShipping=@IsAllDayShipping,IsGenuineProtection=@IsGenuineProtection,IsCashonDelivery=@IsCashonDelivery,IsCreditCard=@IsCreditCard where AutoID=@AutoID";
                SqlParameter[] sqlParams = GetSqlParameter(keyword);
                return ExecuteNonQuery(sql, sqlParams);
            }
            catch
            {
                return 0;
            }
        }

        public static int Delete(NetShopMod keyword)
        {
            return Delete(keyword.AutoID);
        }

        public static int Delete(int autoId)
        {
            try
            {
                string sql = "delete from tab_shops_info where AutoID=" + autoId;
                return ExecuteNonQuery(sql, null);
            }
            catch
            {
                return 0;
            }
        }

        public static int UpdateUse(bool use,string id)
        {
            try
            {
                string sql = "update tab_shops_info set IsUse=" + (use == true ? 1 : 0) + ",UseUpdateDate=getdate() where AutoID=" + id;
                return ExecuteNonQuery(sql, null);
            }
            catch
            {
                return 0;
            }
        }

        public static int UpdateAudit(KeyWordsStatus status, int id)
        {
            try
            {
                string sql = "update tab_shops_info set Status=" + (int)status  + " where AutoID=" + id;
                return ExecuteNonQuery(sql, null);
            }
            catch
            {
                return 0;
            }
        }

        public static NetShopMod CheckKeyWords(NetShopMod keyWords)
        {
            try
            {
                string filter = " UserId=" + keyWords.UserId + " and KeyWords='" + keyWords.KeyWords + "'";
                DataTable dt = QueryFilterToTable(filter);
                if (dt == null || dt.Rows.Count <= 0) return null;
                foreach (DataRow row in dt.Rows)
                {
                    return SetModel(row);
                }
            }
            catch { }
            return null;
        }

         public static IList<NetShopMod> QueryFilterToList(string filter)
        {
            DataTable dt = QueryFilterToTable(filter);
            if (dt == null) return null;
            IList<NetShopMod> lists = new List<NetShopMod>();
            foreach (DataRow row in dt.Rows)
            {
                lists.Add(SetModel(row));
            }
            return lists;
        }

        public static DataTable QueryFilterToTable(string filter)
        {
            try
            {
                string sql = "Select * from tab_shops_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                return ExecuteDataTable(sql);
            }
            catch
            {
            }
            return null;
        }

        #region 模型

        static NetShopMod SetModel(DataRow row)
        {
            NetShopMod keywords = new NetShopMod();
            keywords.AutoID = int.Parse(row["AutoID"].ToString());

            if (row["Commit_Date"] != DBNull.Value)
                keywords.Commit_Date = DateTime.Parse(row["Commit_Date"].ToString());

            if (row["UseUpdateDate"] != DBNull.Value)
                keywords.UseUpdateDate = DateTime.Parse(row["UseUpdateDate"].ToString());

            if (row["KeyWords"] != DBNull.Value)
                keywords.KeyWords = row["KeyWords"].ToString();

            if (row["Status"] != DBNull.Value)
                keywords.Status = (KeyWordsStatus)int.Parse(row["Status"].ToString());

            if (row["IsUse"] != DBNull.Value)
                keywords.IsUse = bool.Parse(row["IsUse"].ToString());

            if (row["UserId"] != DBNull.Value)
                keywords.UserId = int.Parse(row["UserId"].ToString());

            if (row["CommitCount"] != DBNull.Value)
                keywords.CommitCount = int.Parse(row["CommitCount"].ToString());

            if (row["IsAllDayShipping"] != DBNull.Value)
                keywords.IsAllDayShipping = bool.Parse(row["IsAllDayShipping"].ToString());

            if (row["IsCashonDelivery"] != DBNull.Value)
                keywords.IsCashonDelivery = bool.Parse(row["IsCashonDelivery"].ToString());

            if (row["IsCreditCard"] != DBNull.Value)
                keywords.IsCreditCard = bool.Parse(row["IsCreditCard"].ToString());

            if (row["IsGenuineProtection"] != DBNull.Value)
                keywords.IsGenuineProtection = bool.Parse(row["IsGenuineProtection"].ToString());

            if (row["IsLogo"] != DBNull.Value)
                keywords.IsLogo = bool.Parse(row["IsLogo"].ToString());

            if (row["IsProtection"] != DBNull.Value)
                keywords.IsProtection = bool.Parse(row["IsProtection"].ToString());

            if (row["IsRetreat"] != DBNull.Value)
                keywords.IsRetreat = bool.Parse(row["IsRetreat"].ToString());

            if (row["NetShopType"] != DBNull.Value)
                keywords.NetShopType = (ShopType)int.Parse(row["NetShopType"].ToString());

            if (row["ProductName"] != DBNull.Value)
                keywords.ProductName = row["ProductName"].ToString();

            if (row["ProductPicture"] != DBNull.Value)
                keywords.ProductPicture = row["ProductPicture"].ToString();

            if (row["ProductPrice"] != DBNull.Value)
                keywords.ProductPrice = double.Parse(row["ProductPrice"].ToString());

            if (row["QQ"] != DBNull.Value)
                keywords.QQ = row["QQ"].ToString();

            if (row["Region"] != DBNull.Value)
                keywords.Region = row["Region"].ToString();

            if (row["Shipping"] != DBNull.Value)
                keywords.Shipping = double.Parse(row["Shipping"].ToString());

            if (row["ShopName"] != DBNull.Value)
                keywords.ShopName = row["ShopName"].ToString();

            if (row["Status"] != DBNull.Value)
                keywords.Status = (KeyWordsStatus)int.Parse(row["Status"].ToString());

            if (row["Url"] != DBNull.Value)
                keywords.Url = row["Url"].ToString();

            if (row["UserId"] != DBNull.Value)
                keywords.UserId = int.Parse(row["UserId"].ToString());

            if (row["IsPublicbaby"] != DBNull.Value)
                keywords.IsPublicbaby = bool.Parse(row["IsPublicbaby"].ToString());
            
            return keywords;
        }

        static SqlParameter[] GetSqlParameter(NetShopMod keywords)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            SqlParameter AutoID = new SqlParameter("@AutoID", SqlDbType.Int, 4, "AutoID");
            AutoID.Value = keywords.AutoID;
            param.Add(AutoID);

            SqlParameter IsPublicbaby = new SqlParameter("@IsPublicbaby", SqlDbType.Bit, 1, "IsPublicbaby");
            IsPublicbaby.Value = keywords.IsPublicbaby;
            param.Add(IsPublicbaby);

            SqlParameter Commit_Date = new SqlParameter("@Commit_Date", SqlDbType.DateTime, 8, "Commit_Date");
            Commit_Date.Value = keywords.Commit_Date;
            param.Add(Commit_Date);

            SqlParameter IsUse = new SqlParameter("@IsUse", SqlDbType.Bit, 1, "IsUse");
            IsUse.Value = keywords.IsUse;
            param.Add(IsUse);

            SqlParameter KeyWords = new SqlParameter("@KeyWords", SqlDbType.VarChar, 50, "KeyWords");
            KeyWords.Value = keywords.KeyWords;
            param.Add(KeyWords);

            SqlParameter Status = new SqlParameter("@Status", SqlDbType.SmallInt, 4, "Status");
            Status.Value = (int)keywords.Status;
            param.Add(Status);

            SqlParameter UserId = new SqlParameter("@UserId", SqlDbType.Int, 8, "UserId");
            UserId.Value = keywords.UserId;
            param.Add(UserId);

            SqlParameter CommitCount = new SqlParameter("@CommitCount", SqlDbType.Int, 8, "CommitCount");
            CommitCount.Value = keywords.CommitCount;
            param.Add(CommitCount);

            SqlParameter IsAllDayShipping = new SqlParameter("@IsAllDayShipping", SqlDbType.Bit, 1, "IsAllDayShipping");
            IsAllDayShipping.Value = keywords.IsAllDayShipping;
            param.Add(IsAllDayShipping);

            SqlParameter IsCashonDelivery = new SqlParameter("@IsCashonDelivery", SqlDbType.Bit, 1, "IsCashonDelivery");
            IsCashonDelivery.Value = keywords.IsCashonDelivery;
            param.Add(IsCashonDelivery);

            SqlParameter IsCreditCard = new SqlParameter("@IsCreditCard", SqlDbType.Bit, 1, "IsCreditCard");
            IsCreditCard.Value = keywords.IsCreditCard;
            param.Add(IsCreditCard);

            SqlParameter IsGenuineProtection = new SqlParameter("@IsGenuineProtection", SqlDbType.Bit, 1, "IsGenuineProtection");
            IsGenuineProtection.Value = keywords.IsGenuineProtection;
            param.Add(IsGenuineProtection);

            SqlParameter IsLogo = new SqlParameter("@IsLogo", SqlDbType.Bit, 1, "IsLogo");
            IsLogo.Value = keywords.IsLogo;
            param.Add(IsLogo);

            SqlParameter IsProtection = new SqlParameter("@IsProtection", SqlDbType.Bit, 1, "IsProtection");
            IsProtection.Value = keywords.IsProtection;
            param.Add(IsProtection);

            SqlParameter IsRetreat = new SqlParameter("@IsRetreat", SqlDbType.Bit, 1, "IsRetreat");
            IsRetreat.Value = keywords.IsRetreat;
            param.Add(IsRetreat);

            SqlParameter NetShopType = new SqlParameter("@NetShopType", SqlDbType.Int, 8, "NetShopType");
            NetShopType.Value = (int)keywords.NetShopType;
            param.Add(NetShopType);

            SqlParameter ProductName = new SqlParameter("@ProductName", SqlDbType.VarChar, 200, "ProductName");
            ProductName.Value = keywords.ProductName;
            param.Add(ProductName);

            SqlParameter ProductPicture = new SqlParameter("@ProductPicture", SqlDbType.VarChar, 300, "ProductPicture");
            ProductPicture.Value = keywords.ProductPicture;
            param.Add(ProductPicture);

            SqlParameter ProductPrice = new SqlParameter("@ProductPrice", SqlDbType.Money, 8, "ProductPrice");
            ProductPrice.Value = keywords.ProductPrice;
            param.Add(ProductPrice);

            SqlParameter QQ = new SqlParameter("@QQ", SqlDbType.VarChar, 30, "QQ");
            QQ.Value = keywords.QQ;
            param.Add(QQ);

            SqlParameter Region = new SqlParameter("@Region", SqlDbType.VarChar, 200, "Region");
            Region.Value = keywords.Region;
            param.Add(Region);

            SqlParameter Shipping = new SqlParameter("@Shipping", SqlDbType.SmallMoney, 8, "Shipping");
            Shipping.Value = keywords.Shipping;
            param.Add(Shipping);

            SqlParameter ShopName = new SqlParameter("@ShopName", SqlDbType.VarChar, 200, "ShopName");
            ShopName.Value = keywords.ShopName;
            param.Add(ShopName);

            SqlParameter Url = new SqlParameter("@Url", SqlDbType.VarChar, 200, "Url");
            Url.Value = keywords.Url;
            param.Add(Url);

            return param.ToArray();
        }

        #endregion
    }
}